﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

using Excel = Microsoft.Office.Interop.Excel; 

namespace excel_gudi
{
    public partial class Form1 : Form
    {
        private string[] fileNames_;
        public Form1()
        {
            InitializeComponent();
        }

        private void openFile_Click(object sender, EventArgs e)
        {
            listViewFileName.Items.Clear();

            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Multiselect = true;
            ofd.Filter = "Excel File(*.xls, *.xlsx)| *.xls;*xlsx";

            if (ofd.ShowDialog() == DialogResult.OK)
            {
                fileNames_ = ofd.FileNames;
                int len = ofd.FileNames.Length;
                foreach (string str in ofd.FileNames)
                {
                    var tmp = str.Split('\\');
                    int llen = tmp.Length;
                    listViewFileName.Items.Add(tmp[llen - 1]);
                }

            }
            else
                return;
            if(fileNames_.Length > 0)
                labelInfo.Text = "You have chosen " + fileNames_.Length + " excel files.";
        }

        private void copySheet_Click(object sender, EventArgs e)
        {
            if (fileNames_.Length <= 0)
                return;

            FolderBrowserDialog fbd = new FolderBrowserDialog();
            fbd.Description = "Please choose one folder to save the files";
            string folderPath = "d:\\";
            if (fbd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                folderPath = fbd.SelectedPath;
            }
            else
            {
            }


            labelInfo.Text = "Processing, please wait...";

            Excel.Application xlApp;
            Excel.Workbook xlWorkBookTarget;
            xlApp = new Excel.Application();
            

            int index = 1;
            string[] sheetNames;

            Excel.Workbook xlWorkBookFirst;
            xlWorkBookFirst = xlApp.Workbooks.Open(fileNames_[0]);
            int sheetNum = xlWorkBookFirst.Worksheets.Count;

            sheetNames = new string[sheetNum];

            for (int i = 0; i < sheetNum; i++)
            {
                sheetNames[i] = xlWorkBookFirst.Worksheets[i + 1].Name;
            }

            xlWorkBookFirst.Close();

            for (int i = 0; i < sheetNum; i++)
            {
                index = 1;
                xlWorkBookTarget = xlApp.Workbooks.Add();
                foreach (string str in fileNames_)
                {
                    Excel.Workbook xlWorkBookTmp;
                    xlWorkBookTmp = xlApp.Workbooks.Open(str);
                    if (xlWorkBookTmp.Worksheets.Count < sheetNum)
                    {
                        xlWorkBookTmp.Close();
                        continue;
                    }
                    Excel.Worksheet sheet = xlWorkBookTmp.Worksheets[i + 1];

                    sheet.Copy(xlWorkBookTarget.Worksheets[index]);

                    var strPart = str.Split('\\');
                    var strNameWithExt = strPart[strPart.Length - 1];
                    strPart = strNameWithExt.Split('.');
                    var sheetName = strPart[0];

                    xlWorkBookTarget.Worksheets[index].Name = sheetName;

                    xlWorkBookTmp.Close();
                    index++;

                }
                string saveName = folderPath + "\\" +  sheetNames[i] + ".xlsx";
                xlWorkBookTarget.SaveAs(saveName);
                xlWorkBookTarget.Close();
            }
            xlApp.Quit();
            labelInfo.Text = "Finished! Please check the folder.";
            //if (fileNames_.Length > 0)
            //{
            //    Excel.Application xlAppIn;
            //    Excel.Application xlApp;
            //    Excel.Workbook xlWorkBook;
            //    Excel.Workbook xlWorkBookIn;
            //    Excel.Worksheet xlWorkSheet;
            //    Excel.Worksheet xlWorkSheetIn;
            //    object misValue = System.Reflection.Missing.Value;


            //    xlApp = new Excel.Application();
            //    xlWorkBookIn = xlApp.Workbooks.Open(fileNames_[0]);
            //    xlWorkSheetIn = xlWorkBookIn.Worksheets[1];

            //    xlWorkBook = xlApp.Workbooks.Add();
            //   // xlWorkSheetIn = xlWorkBookIn.Worksheets[1];

            //    foreach (Excel.Worksheet displayWorksheet in xlWorkBookIn.Worksheets)
            //    {
            //        listViewFileName.Items.Add(displayWorksheet.Name);
                   
            //    }

            //    xlWorkBook = xlApp.Workbooks.Add();
            //    //xlWorkSheet = xlWorkBook.Worksheets[1];
            //    ////xlWorkSheet = xlWorkBookIn;
            //    xlWorkSheetIn.Copy(xlWorkBook.Worksheets[1]);

               
            //    xlWorkBook.SaveAs("d:\\test.xlsx");
            //   // xlWorkBook.Close(true, misValue, misValue);
            //    xlApp.Quit();

            //    //releaseObject(xlWorkSheet);
            //    //releaseObject(xlWorkBook);
            //    //releaseObject(xlApp);
            //}
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            labelInfo.Text = "";
        }
    }
}
